USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ChangePassword')
	BEGIN
		DROP  Procedure  dbo.ChangePassword
	END
GO

CREATE Procedure dbo.ChangePassword (
	@LOGINID				VARCHAR(50)
,	@PASSWORD				VARCHAR(20)
,	@NEWPASSWORD			VARCHAR(20)
)
AS
BEGIN
	IF NOT EXISTS(SELECT 1 FROM TR_USER WHERE LOGINID = @LOGINID AND [PASSWORD]=@PASSWORD AND ISACTIVE=1)
	BEGIN
		SELECT 
			'<DATA>
				<RESPONSE>
					<RESULT>E</RESULT>
					<MESSAGE><![CDATA[Invalid Login ID or Password or User is Inactive]]></MESSAGE>
				</RESPONSE>
			</DATA>' AS Result
		RETURN
	END

	BEGIN TRAN
		BEGIN TRY
			UPDATE TR_USER
			SET	[Password]	= @NEWPASSWORD
			FROM TR_USER
			WHERE 
				LOGINID		= @LOGINID
			AND ISACTIVE	= 1

		COMMIT TRAN

		SELECT 
			'<DATA>
				<RESPONSE>
					<RESULT>S</RESULT>
					<MESSAGE>Password changed successfully</MESSAGE>
				</RESPONSE>
			</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to change the password. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
